# -*- coding: utf-8 -*-
"""
财产保险成本费用指标计算
工单编号：大数据-八维保险数据挖掘-04-财产保险成本费用相关
功能：计算财产保险各项成本费用指标，支持按七日、月、年统计
"""

import mysql.connector
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import logging

# 配置日志
logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
                    filename='insurance_cost_analysis.log')
logger = logging.getLogger(__name__)


class InsuranceCostAnalyzer:
    """财产保险成本费用分析器"""

    def __init__(self, host="localhost", user="root", password="root", database="insurance_03"):
        """初始化数据库连接"""
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.connect_db()

    def connect_db(self):
        """连接MySQL数据库"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            logger.info("数据库连接成功")
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise

    def close_db(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            logger.info("数据库连接已关闭")

    def get_data_by_period(self, start_date, end_date, region_id=None):
        """
        获取指定时间段内的业务数据

        参数:
            start_date: 开始日期，格式'YYYY-MM-DD'
            end_date: 结束日期，格式'YYYY-MM-DD'
            region_id: 区域ID，可选

        返回:
            包含保单、费用、赔付和准备金数据的字典
        """
        try:
            # 构建区域过滤条件
            region_filter = f"AND region_id = '{region_id}'" if region_id else ""

            # 查询保单数据
            policy_sql = f"""
            SELECT 
                SUM(original_premium) AS original_premium,
                SUM(ceded_premium) AS ceded_premium
            FROM property_policy
            WHERE issue_date BETWEEN '{start_date}' AND '{end_date}'
            {region_filter}
            """

            # 查询费用数据
            expense_sql = f"""
            SELECT 
                SUM(business_management_fee) AS business_management_fee,
                SUM(commission_fee) AS commission_fee,
                SUM(reinsurance_fee) AS reinsurance_fee,
                SUM(tax_fee) AS tax_fee,
                SUM(recovered_reinsurance_fee) AS recovered_reinsurance_fee
            FROM property_expense
            WHERE expense_date BETWEEN '{start_date}' AND '{end_date}'
            {region_filter}
            """

            # 查询赔付数据
            claim_sql = f"""
            SELECT 
                SUM(claim_amount) AS claim_amount
            FROM property_claim
            WHERE claim_date BETWEEN '{start_date}' AND '{end_date}'
            {region_filter}
            """

            # 查询准备金数据
            reserve_sql = f"""
            SELECT 
                SUM(unearned_premium_reserve_extract) AS unearned_extract,
                SUM(unearned_premium_reserve_reverse) AS unearned_reverse,
                SUM(outstanding_claim_reserve_extract) AS outstanding_extract,
                SUM(outstanding_claim_reserve_reverse) AS outstanding_reverse
            FROM property_reserve
            WHERE reserve_date BETWEEN '{start_date}' AND '{end_date}'
            {region_filter}
            """

            # 执行查询
            with self.connection.cursor(dictionary=True) as cursor:
                cursor.execute(policy_sql)
                policy_data = cursor.fetchone()

                cursor.execute(expense_sql)
                expense_data = cursor.fetchone()

                cursor.execute(claim_sql)
                claim_data = cursor.fetchone()

                cursor.execute(reserve_sql)
                reserve_data = cursor.fetchone()

            # 处理空值
                # 处理空值并转换为float类型
                for data in [policy_data, expense_data, claim_data, reserve_data]:
                    for key, value in data.items():
                        if value is None:
                            data[key] = 0.0  # 改为float类型的0.0
                        else:
                            # 将decimal.Decimal转换为float
                            data[key] = float(value)  # 新增这一行

                return {
                    'policy': policy_data,
                    'expense': expense_data,
                    'claim': claim_data,
                    'reserve': reserve_data
                }

        except Exception as e:
            logger.error(f"获取数据失败: {str(e)}")
            raise

    def calculate_earned_premium(self, policy_data, reserve_data):
        """
        计算已赚保费

        公式: 已赚保费 = 原保费收入 - 分出保费 - 提取未到期责任准备金 + 转回未到期责任准备金
        """
        earned_premium = (policy_data['original_premium'] - policy_data['ceded_premium'] -
                          reserve_data['unearned_extract'] + reserve_data['unearned_reverse'])
        return max(earned_premium, 0.0001)  # 避免除以零

    def calculate_indicators(self, data):
        """
        计算各项成本费用指标

        参数:
            data: 包含保单、费用、赔付和准备金数据的字典

        返回:
            包含各项指标的字典
        """
        # 提取数据
        policy = data['policy']
        expense = data['expense']
        claim = data['claim']
        reserve = data['reserve']

        # 计算已赚保费
        earned_premium = self.calculate_earned_premium(policy, reserve)

        # 计算赔付率
        claim_payout = claim['claim_amount']
        net_reserve = reserve['outstanding_extract'] - reserve['outstanding_reverse']
        loss_ratio = (claim_payout + net_reserve) / earned_premium * 100

        # 计算综合费用率
        total_expense = (expense['business_management_fee'] + expense['commission_fee'] +
                         expense['reinsurance_fee'] + expense['tax_fee'] -
                         expense['recovered_reinsurance_fee'])
        comprehensive_expense_ratio = total_expense / earned_premium * 100

        # 计算综合成本率
        comprehensive_cost_ratio = (claim_payout + net_reserve + total_expense) / earned_premium * 100

        # 计算保费费用率
        premium_income = policy['original_premium']
        premium_expense_ratio = expense['business_management_fee'] / (
            premium_income if premium_income > 0 else 0.0001) * 100

        # 计算手续费及佣金比率
        commission_ratio = expense['commission_fee'] / (premium_income if premium_income > 0 else 0.0001) * 100

        # 计算分保费用比率
        reinsurance_premium = policy['ceded_premium']
        reinsurance_ratio = expense['reinsurance_fee'] / (
            reinsurance_premium if reinsurance_premium > 0 else 0.0001) * 100

        # 增加业务合理性校验：若保费收入过小，标记为异常
        if premium_income < 1000:  # 假设最小保费为1000元
            logger.warning(f"原保费收入异常（{premium_income}元），可能导致指标失真")

        # 计算保费费用率（若费用远高于保费，限制最大值并标记）
        premium_expense_ratio = expense['business_management_fee'] / (
            premium_income if premium_income > 0 else 0.0001) * 100
        if premium_expense_ratio > 100:  # 实际业务中极少超过100%
            premium_expense_ratio = round(premium_expense_ratio, 2)
            logger.warning(f"保费费用率异常（{premium_expense_ratio}%），可能数据有误")

        # 计算分保费用比率（同理限制）
        reinsurance_ratio = expense['reinsurance_fee'] / (
            reinsurance_premium if reinsurance_premium > 0 else 0.0001) * 100
        if reinsurance_ratio > 50:  # 分保费用通常不超过分保费的50%
            reinsurance_ratio = round(reinsurance_ratio, 2)
            logger.warning(f"分保费用比率异常（{reinsurance_ratio}%），可能数据有误")

        # 综合成本率限制（超过200%标记异常）
        if comprehensive_cost_ratio > 200:
            comprehensive_cost_ratio = round(comprehensive_cost_ratio, 2)
            logger.warning(f"综合成本率异常（{comprehensive_cost_ratio}%），可能数据有误")

        return {
            'period': '',  # 后续填充时间段
            'region_id': '',  # 后续填充区域ID
            'original_premium': round(policy['original_premium'], 2),
            'earned_premium': round(earned_premium, 2),
            'loss_ratio': round(loss_ratio, 2),
            'comprehensive_expense_ratio': round(comprehensive_expense_ratio, 2),
            'comprehensive_cost_ratio': round(comprehensive_cost_ratio, 2),
            'premium_expense_ratio': round(premium_expense_ratio, 2),
            'commission_ratio': round(commission_ratio, 2),
            'reinsurance_ratio': round(reinsurance_ratio, 2)
        }

    def analyze_by_week(self, end_date, weeks=1, region_id=None):
        """
        按周统计指标

        参数:
            end_date: 结束日期，格式'YYYY-MM-DD'
            weeks: 统计周数
            region_id: 区域ID，可选

        返回:
            包含每周指标的列表
        """
        result = []
        end = datetime.strptime(end_date, '%Y-%m-%d')

        for i in range(weeks):
            start = end - timedelta(days=6)
            start_str = start.strftime('%Y-%m-%d')
            end_str = end.strftime('%Y-%m-%d')

            # 获取该周数据
            data = self.get_data_by_period(start_str, end_str, region_id)

            # 计算指标
            indicators = self.calculate_indicators(data)
            indicators['period'] = f"{start_str}至{end_str}"
            indicators['region_id'] = region_id or '全部区域'

            result.append(indicators)

            # 移动到上一周
            end = start - timedelta(days=1)

        # 按时间升序排列
        return sorted(result, key=lambda x: x['period'])

    def analyze_by_month(self, end_date, months=1, region_id=None):
        """
        按月统计指标

        参数:
            end_date: 结束日期，格式'YYYY-MM-DD'
            months: 统计月数
            region_id: 区域ID，可选

        返回:
            包含每月指标的列表
        """
        result = []
        end = datetime.strptime(end_date, '%Y-%m-%d')

        for i in range(months):
            # 计算当月第一天
            first_day = datetime(end.year, end.month, 1)

            # 如果是1月份，回退到上一年的12月份
            if end.month == 1:
                prev_month = 12
                prev_year = end.year - 1
            else:
                prev_month = end.month - 1
                prev_year = end.year

            # 计算上月最后一天
            if prev_month == 12:
                last_day_prev_month = datetime(prev_year, prev_month, 31)
            else:
                last_day_prev_month = datetime(prev_year, prev_month + 1, 1) - timedelta(days=1)

            start_str = first_day.strftime('%Y-%m-%d')
            end_str = end.strftime('%Y-%m-%d')

            # 获取该月数据
            data = self.get_data_by_period(start_str, end_str, region_id)

            # 计算指标
            indicators = self.calculate_indicators(data)
            indicators['period'] = f"{end.year}年{end.month}月"
            indicators['region_id'] = region_id or '全部区域'

            result.append(indicators)

            # 移动到上月
            end = last_day_prev_month

        # 按时间升序排列
        return sorted(result, key=lambda x: x['period'])

    def analyze_by_year(self, end_year, years=1, region_id=None):
        """
        按年统计指标

        参数:
            end_year: 结束年份，整数
            years: 统计年数
            region_id: 区域ID，可选

        返回:
            包含每年指标的列表
        """
        result = []

        for i in range(years):
            current_year = end_year - i
            start_str = f"{current_year}-01-01"
            end_str = f"{current_year}-12-31"

            # 获取该年数据
            data = self.get_data_by_period(start_str, end_str, region_id)

            # 计算指标
            indicators = self.calculate_indicators(data)
            indicators['period'] = f"{current_year}年"
            indicators['region_id'] = region_id or '全部区域'

            result.append(indicators)

        # 按时间升序排列
        return sorted(result, key=lambda x: x['period'])


# 示例用法
if __name__ == "__main__":
    # 初始化分析器
    analyzer = InsuranceCostAnalyzer(
        host='localhost',
        user='root',
        password='root',
        database='insurance_03'
    )

    try:
        # 分析最近12周的数据
        weekly_indicators = analyzer.analyze_by_week('2025-06-30', weeks=12, region_id='0101')
        print("周度指标分析结果:")
        for idx, item in enumerate(weekly_indicators):
            print(f"\n第{idx + 1}周: {item['period']}")
            print(f"赔付率: {item['loss_ratio']}%")
            print(f"综合费用率: {item['comprehensive_expense_ratio']}%")
            print(f"综合成本率: {item['comprehensive_cost_ratio']}%")

        # 分析最近6个月的数据
        monthly_indicators = analyzer.analyze_by_month('2025-06-30', months=6)
        print("\n\n月度指标分析结果:")
        for item in monthly_indicators:
            print(f"\n{item['period']}")
            print(f"保费费用率: {item['premium_expense_ratio']}%")
            print(f"手续费及佣金比率: {item['commission_ratio']}%")

        # 分析最近3年的数据
        yearly_indicators = analyzer.analyze_by_year(2025, years=3)
        print("\n\n年度指标分析结果:")
        for item in yearly_indicators:
            print(f"\n{item['period']}")
            print(f"分保费用比率: {item['reinsurance_ratio']}%")
            print(f"综合成本率: {item['comprehensive_cost_ratio']}%")

    finally:
        # 关闭数据库连接
        analyzer.close_db()